﻿CREATE PROCEDURE [dbo].[SearchByColor] 
(
	@Range0L FLOAT, @Range0H FLOAT,
	@Range1L FLOAT, @Range1H FLOAT,
	@Range2L FLOAT, @Range2H FLOAT,
	@Range3L FLOAT, @Range3H FLOAT,
	@Range4L FLOAT, @Range4H FLOAT,
	@Range5L FLOAT, @Range5H FLOAT,
	@Range6L FLOAT, @Range6H FLOAT,
	@Range7L FLOAT, @Range7H FLOAT,
	@Percent FLOAT
)
AS 
	-- Get the color spectrums that fall between the range values for each color definition
	WITH Spectrums AS (
		SELECT [ImageAnalysisId], [DefinitionId], [PercentWithinDistance]
		FROM [dbo].[ColorSpectrum]
		WHERE [DefinitionId] = 0 AND [AverageDistance] BETWEEN @Range0L AND @Range0H
		UNION ALL
		SELECT [ImageAnalysisId], [DefinitionId], [PercentWithinDistance]
		FROM [dbo].[ColorSpectrum]
		WHERE [DefinitionId] = 1 AND [AverageDistance] BETWEEN @Range1L AND @Range1H
		UNION ALL
		SELECT [ImageAnalysisId], [DefinitionId], [PercentWithinDistance]
		FROM [dbo].[ColorSpectrum]
		WHERE [DefinitionId] = 2 AND [AverageDistance] BETWEEN @Range2L AND @Range2H
		UNION ALL
		SELECT [ImageAnalysisId], [DefinitionId], [PercentWithinDistance]
		FROM [dbo].[ColorSpectrum]
		WHERE [DefinitionId] = 3 AND [AverageDistance] BETWEEN @Range3L AND @Range3H
		UNION ALL
		SELECT [ImageAnalysisId], [DefinitionId], [PercentWithinDistance]
		FROM [dbo].[ColorSpectrum]
		WHERE [DefinitionId] = 4 AND [AverageDistance] BETWEEN @Range4L AND @Range4H
		UNION ALL
		SELECT [ImageAnalysisId], [DefinitionId], [PercentWithinDistance]
		FROM [dbo].[ColorSpectrum]
		WHERE [DefinitionId] = 5 AND [AverageDistance] BETWEEN @Range5L AND @Range5H
		UNION ALL
		SELECT [ImageAnalysisId], [DefinitionId], [PercentWithinDistance]
		FROM [dbo].[ColorSpectrum]
		WHERE [DefinitionId] = 6 AND [AverageDistance] BETWEEN @Range6L AND @Range6H
		UNION ALL
		SELECT [ImageAnalysisId], [DefinitionId], [PercentWithinDistance]
		FROM [dbo].[ColorSpectrum]
		WHERE [DefinitionId] = 7 AND [AverageDistance] BETWEEN @Range7L AND @Range7H
	), 
	PSpectrums AS (
		-- For each image analysis and color, make sure the percentage sums up to the passed
		-- percent of image parameter (thus divide by 100 because there are 100 sections in the image)
		SELECT [ImageAnalysisId], [DefinitionId]
		FROM Spectrums
		GROUP BY [ImageAnalysisId], [DefinitionId]
		HAVING (SUM([PercentWithinDistance]) / 100) > @Percent
	),
	DSpectrums AS (
		-- Get all Image Analysis IDs were all 8 color spectrums meet the criteria
		SELECT [ImageAnalysisId]
		FROM PSpectrums
		GROUP BY [ImageAnalysisId]
		HAVING COUNT(DISTINCT [DefinitionId]) = 8
	),
	Images AS (
		-- Return all of the image identifiers for the resulting analysis records
		SELECT [ImageId] 
		FROM DSpectrums D
		INNER JOIN [dbo].[ImageAnalysis] IA ON D.[ImageAnalysisId] = IA.[Id]
	)
	-- Select the images that were found by the search excluding private images
	SELECT I.* 
	FROM Images IMG
	INNER JOIN [dbo].[Image] I ON IMG.[ImageId] = I.[Id]
	WHERE I.[IsPrivate] = 0 AND I.[IsFolderPrivate] = 0

RETURN 0